﻿using EasyERP_Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EasyERP_DAL.Wares
{
    /// <summary>
    /// 商品信息管理数据访问类
    /// </summary>
    public class B_Item_DAL
    {
        #region 查询商品信息表
        /// <summary>
        /// 查询商品信息表
        /// </summary>
        /// <param name="mdoel">商品信息实体对象</param>
        /// <returns>返回商品信息表</returns>
        public DataTable Get_B_Item(B_Item model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT item_cd,
                       item_nm,
                       item_spec,
                       item_price,
                       item_unit,
                       dbo.Get_Minor_nm(N'1003', item_unit) AS item_unit_nm,
                       item_group,
                       dbo.Get_Minor_nm(N'1004', item_group) AS item_group_nm,
                       sl_cd,
                       dbo.Get_Minor_nm(N'1005', sl_cd) AS sl_cd_nm,
                       remark
                FROM dbo.b_item
                WHERE item_nm LIKE N'%{model.item_nm}%'
                      AND CASE
                              WHEN N'{model.item_group}' = '' THEN
                                  ''
                              ELSE
                                  item_group
                          END = N'{model.item_group}';
            ");

            return DbHelperSQL.Query(sb.ToString()).Tables[0];
        }
        #endregion

        #region 添加商品信息
        /// <summary>
        /// 添加商品信息
        /// </summary>
        /// <param name="model">商品信息实体对象</param>
        /// <returns>返回True：成功；返回False：失败</returns>
        public bool Insert_B_Item(B_Item model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                INSERT INTO dbo.b_item
                (
                    item_cd,
                    item_nm,
                    item_spec,
                    item_price,
                    item_unit,
                    item_group,
                    sl_cd,
                    remark,
                    ins_user,
                    ins_date,
                    up_user,
                    up_date
                )
                VALUES
                (   N'{model.item_cd}',         -- item_cd - nvarchar(10)
                    N'{model.item_nm}',         -- item_nm - nvarchar(50)
                    N'{model.item_spec}',       -- item_spec - nvarchar(100)
                    {model.item_price},         -- item_price - numeric(18, 4)
                    N'{model.item_unit}',       -- item_unit - nvarchar(4)
                    N'{model.item_group}',      -- item_group - nvarchar(4)
                    N'{model.sl_cd}',           -- sl_cd - nvarchar(4)
                    N'{model.remark}',          -- remark - nvarchar(200)
                    N'{model.user_cd}',         -- ins_user - nvarchar(6)
                    GETDATE(),                  -- ins_date - datetime
                    N'{model.user_cd}',         -- up_user - nvarchar(6)
                    GETDATE()                   -- up_date - datetime
                    );
            ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 删除商品信息
        /// <summary>
        /// 删除商品信息
        /// </summary>
        /// <param name="model">商品信息实体对象</param>
        /// <returns>返回True：成功；返回False：失败</returns>
        public bool Delete_B_Item(B_Item model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                DELETE FROM dbo.b_item
                WHERE item_cd = N'{model.item_cd}';
             ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 修改商品信息
        /// <summary>
        /// 修改商品信息
        /// </summary>
        /// <param name="model">商品信息实体对象</param>
        /// <returns>返回True：成功；返回False：失败</returns>
        public bool Update_B_Item(B_Item model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                UPDATE dbo.b_item
                SET item_nm = N'{model.item_nm}',
                    item_spec = N'{model.item_spec}',
                    item_price = {model.item_price},
                    item_unit = N'{model.item_unit}',
                    item_group = N'{model.item_group}',
                    sl_cd = N'{model.sl_cd}',
                    remark = N'{model.remark}',
                    up_user = N'{model.user_cd}',
                    up_date = GETDATE()
                WHERE item_cd = N'{model.item_cd}';
             ");

            return DbHelperSQL.ExecuteSql(sb.ToString()) > 0;
        }
        #endregion

        #region 查询数据是否存在
        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="model">商品信息实体对象</param>
        /// <returns>返回True：存在；返回False：不存在</returns>
        public bool Exists(B_Item model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT 1
                FROM dbo.b_item
                WHERE item_cd = N'{model.item_cd}';
            ");

            return DbHelperSQL.Exists(sb.ToString());
        }
        #endregion

        #region 删除商品信息前，确认采购订单中是否存在使用该商品信息的
        /// <summary>
        /// 删除商品信息前，确认采购订单中是否存在使用该商品信息的
        /// </summary>
        /// <param name="model">商品信息实体对象，仅item_cd字段</param>
        /// <returns>返回True：存在，不允许删除；返回False：不存在</returns>
        public bool Exists_Po_dtl(B_Item model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT 1
                FROM dbo.po_dtl
                WHERE item_cd = N'{model.item_cd}';
            ");

            return DbHelperSQL.Exists(sb.ToString());
        }
        #endregion

        #region 删除商品信息前，确认销售订单中是否存在使用该商品信息的
        /// <summary>
        /// 删除商品信息前，确认销售订单中是否存在使用该商品信息的
        /// </summary>
        /// <param name="model">商品信息实体对象，仅item_cd字段</param>
        /// <returns>返回True：存在，不允许删除；返回False：不存在</returns>
        public bool Exists_So_dtl(B_Item model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT 1
                FROM dbo.so_dtl
                WHERE item_cd = N'{model.item_cd}';
            ");

            return DbHelperSQL.Exists(sb.ToString());
        }
        #endregion

        #region 删除商品信息前，确认商品单价中是否存在使用该商品信息的
        /// <summary>
        /// 删除商品信息前，确认商品单价中是否存在使用该商品信息的
        /// </summary>
        /// <param name="model">商品信息实体对象，仅item_cd字段</param>
        /// <returns>返回True：存在，不允许删除；返回False：不存在</returns>
        public bool Exists_B_Item_Price(B_Item model)
        {
            //sql语句
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat($@"
                SELECT 1
                FROM dbo.b_item_price
                WHERE item_cd = N'{model.item_cd}';
            ");

            return DbHelperSQL.Exists(sb.ToString());
        } 
        #endregion
    }
}
